package cc.magicjson.scenario.implement.module.jdbc.template.manager;

import cc.magicjson.scenario.implement.module.jdbc.template.exception.SqlNotFoundException;
import cc.magicjson.scenario.implement.module.jdbc.template.provider.SqlParameterInfo;
import cc.magicjson.scenario.implement.module.jdbc.template.provider.SqlProvider;
import com.google.common.collect.Lists;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 批量操作执行器
 */
@Component
@Slf4j
@Getter
public class BatchOperations {
    private final JdbcTemplate jdbcTemplate;
    private final TransactionTemplate transactionTemplate;
    private final SqlProvider sqlProvider;

    public BatchOperations(JdbcTemplate jdbcTemplate,
                           PlatformTransactionManager transactionManager,
                           SqlProvider sqlProvider) {
        this.jdbcTemplate = jdbcTemplate;
        this.transactionTemplate = new TransactionTemplate(transactionManager);
        this.sqlProvider = sqlProvider;

        // 配置事务
        this.transactionTemplate.setPropagationBehavior(
            TransactionDefinition.PROPAGATION_REQUIRED);
        this.transactionTemplate.setIsolationLevel(
            TransactionDefinition.ISOLATION_READ_COMMITTED);
    }

    /**
     * 执行批量操作
     *
     * @param items     数据项列表
     * @param sqlKey    SQL键值
     * @param extractor 参数提取器
     * @param <T>       数据类型
     * @return 影响的行数
     */
    public <T> Integer executeBatch(List<T> items,
                                    String sqlKey,
                                    BatchParameterExtractor<T> extractor) {
        if (CollectionUtils.isEmpty(items)) {
            return 0;
        }

        return transactionTemplate.execute(status -> {
            try {
                String sql = sqlProvider.getSql(sqlKey, items.get(0));
                long placeholderCount = sql.chars().filter(ch -> ch == '?').count();
                log.debug("SQL: {} has {} placeholders", sql, placeholderCount);

                return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Object[] rawParams = extractor.extract(items.get(i));
                        if (rawParams == null) {
                            throw new SQLException("Parameter array cannot be null for item at index " + i);
                        }

                        // 过滤掉null值并创建新数组
                        Object[] allParams = Arrays.stream(rawParams)
                                .filter(Objects::nonNull)
                                .toArray();

                        // 检查参数数量
                        if (allParams.length < placeholderCount) {
                            throw new SQLException(String.format(
                                "Not enough non-null parameters. Expected %d but got %d for item at index %d",
                                placeholderCount, allParams.length, i));
                        }

                        // 设置参数
                        for (int j = 0; j < placeholderCount; j++) {
                            try {
                                ps.setObject(j + 1, allParams[j]);
                            } catch (SQLException e) {
                                log.error("Error setting parameter {} with value {}", j + 1, allParams[j], e);
                                throw e;
                            }
                        }

                        if (log.isDebugEnabled()) {
                            Object[] usedParams = Arrays.copyOfRange(allParams, 0, (int)placeholderCount);
                            log.debug("Batch item {}: using parameters = {}", i, Arrays.toString(usedParams));
                        }
                    }

                    @Override
                    public int getBatchSize() {
                        return items.size();
                    }
                }).length;
            } catch (Exception e) {
                log.error("Batch operation failed", e);
                status.setRollbackOnly();
                throw new RuntimeException("Batch operation failed", e);
            }
        });
    }

    /**
     * 分批执行大批量操作
     */
    public <T> int executeLargeBatch(List<T> items,
                                     String sqlKey,
                                     BatchParameterExtractor<T> parameterExtractor,
                                     int batchSize) {
        return Lists.partition(items, batchSize)
            .stream()
            .mapToInt(batch -> executeBatch(batch, sqlKey, parameterExtractor))
            .sum();
    }

    /**
     * 参数提取器接口
     */
    @FunctionalInterface
    public interface BatchParameterExtractor<T> {
        Object[] extract(T item);
    }

    /**
     * 动态批量操作
     */
    public <T> Integer executeDynamicBatch(List<T> items, String sqlKey, BatchParameterExtractor<T> extractor) {
        if (CollectionUtils.isEmpty(items)) {
            log.warn("No items to process for SQL key: {}", sqlKey);
            return 0;
        }

        return transactionTemplate.execute(status -> {
            try {
                log.info("Starting batch operation for key [{}] with {} items", sqlKey, items.size());

                Map<String, List<T>> groupedItems = new HashMap<>();
                // 预处理所有SQL
                for (T item : items) {
                    String sql = sqlProvider.getSql(sqlKey, item);
                    groupedItems.computeIfAbsent(sql, k -> new ArrayList<>()).add(item);
                }

                log.info("Grouped items into {} different SQL statements", groupedItems.size());

                int totalAffected = 0;
                // 分组执行批处理
                for (Map.Entry<String, List<T>> entry : groupedItems.entrySet()) {
                    String sql = entry.getKey();
                    List<T> groupItems = entry.getValue();

                    log.info("Executing batch for SQL: {} with {} items", sql, groupItems.size());

                    int[] affected = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                        @Override
                        public void setValues(PreparedStatement ps, int i) throws SQLException {
                            Object[] params = extractor.extract(groupItems.get(i));
                            if (log.isDebugEnabled()) {
                                log.debug("Batch item {}: parameters = {}", i, Arrays.toString(params));
                            }
                            for (int j = 0; j < params.length; j++) {
                                ps.setObject(j + 1, params[j]);
                            }
                        }

                        @Override
                        public int getBatchSize() {
                            return groupItems.size();
                        }
                    });

                    int groupAffected = Arrays.stream(affected).sum();
                    totalAffected += groupAffected;
                    log.info("Batch execution completed for group, affected rows: {}", groupAffected);
                }

                log.info("Batch operation completed for key [{}], total affected rows: {}",
                    sqlKey, totalAffected);
                return totalAffected;

            } catch (Exception e) {
                log.error("Batch operation failed for key [{}]", sqlKey, e);
                status.setRollbackOnly();
                throw new RuntimeException(e);
            }
        });
    }

    /**
     * 执行批量操作（自动参数提取版本）
     */
    public <T> Integer executeAutoBatch(List<T> items, String sqlKey) {
        if (CollectionUtils.isEmpty(items)) {
            log.warn("No items to process for SQL key: {}", sqlKey);
            return 0;
        }

        return transactionTemplate.execute(status -> {
            try {
                T firstItem = items.get(0);
                String sql = sqlProvider.getSql(sqlKey, firstItem);
                List<SqlParameterInfo> parameterInfos = sqlProvider.getSqlParameterInfos(sqlKey, firstItem);

                log.debug("Executing batch operation with SQL key: {}, items count: {}", sqlKey, items.size());

                return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        T item = items.get(i);
                        setParameterValues(ps, item, parameterInfos, i);
                    }

                    @Override
                    public int getBatchSize() {
                        return items.size();
                    }
                }).length;
            } catch (Exception | SqlNotFoundException e) {
                log.error("Batch operation failed for SQL key: {}", sqlKey, e);
                status.setRollbackOnly();
                throw new RuntimeException("Batch operation failed", e);
            }
        });
    }

    private <T> void setParameterValues(PreparedStatement ps, T item,
                                      List<SqlParameterInfo> parameterInfos,
                                      int batchIndex) throws SQLException {
        try {
            for (int j = 0; j < parameterInfos.size(); j++) {
                SqlParameterInfo paramInfo = parameterInfos.get(j);
                Object value = extractValue(item, paramInfo.getPropertyName());

                // 直接使用setObject，让JDBC驱动处理类型转换
                ps.setObject(j + 1, value);

//                if (log.isDebugEnabled()) {
//                    log.debug("Parameter set for batch item {}, index {}: {} = {}",
//                        batchIndex, j + 1, paramInfo.getPropertyName(), value);
//                }
            }
        } catch (Exception e) {
            log.error("Error setting parameters for batch item {} with values", batchIndex, e);
            throw new SQLException("Failed to set parameters", e);
        }
    }

    private Object extractValue(Object item, String propertyName) {
        try {
            if (item instanceof Map) {
                return ((Map<?, ?>) item).get(propertyName);
            }

            if (!StringUtils.hasText(propertyName)) {
                return null;
            }

            // 使用Spring的BeanWrapper
            BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(item);
            beanWrapper.setAutoGrowNestedPaths(true); // 允许自动创建嵌套路径

            return beanWrapper.getPropertyValue(propertyName);

        } catch (Exception e) {
            log.error("Failed to extract value for property: {}", propertyName, e);
            throw new RuntimeException("Property extraction failed: " + propertyName, e);
        }
    }
}
